-- @owner: @nanyang12
-- @date: 2024/10/23
-- @testpoint:start with connect by record子查询

--step1:建表
drop table if exists t_connect_by_0028_01,t_connect_by_0028_02; create table t_connect_by_0028_01(id int,pid int);
create table t_connect_by_0028_02(id int,pid int);
--step2:插入数据
insert into t_connect_by_0028_01 values(141,131),(131,121),(121,111),(111,11),(11,1),(1,0);
insert into t_connect_by_0028_02 values(141,131),(131,121),(121,111),(111,11),(11,1),(1,0);
--step3:start with connect by record子查询
select t1.id,t1.pid,t2.id,t2.pid from t_connect_by_0028_01 t1 join t_connect_by_0028_02 t2 on t1.id=t2.id where row(1,0)>=(select id,pid from t_connect_by_0028_02 start
with id=1 connect by prior id=pid and level=1) start with t1.id=141 connect by prior t2.pid=t1.id;
--step4:清理环境
drop table if exists t_connect_by_0028_01,t_connect_by_0028_02;


